package jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author lzy
 * @create 2018-07-31 下午2:24
 */
public class ConnectionPoolTest {
    private static final Logger LOGGER = LoggerFactory.getLogger(ConnectionPoolTest.class);

    @Test
    public void testJdbcUtils() throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        LOGGER.info(connection.toString());
    }

    /**
     * 1. 创建c3p0-config.xml配置文件，参考帮助文档中附录B的内容
     * 2. 创建ComboPooledDataSource实例
     * DataSource dataSource = new ComboPooledDataSource("helloc3p0");
     * 3. 从DataSource实例中获取连接
     *
     * @throws SQLException
     */
    @Test
    public void testC3P0withConfigFile() throws SQLException {
        DataSource dataSource = new ComboPooledDataSource("helloc3p0");
        LOGGER.info(dataSource.getConnection().toString());
    }

    @Test
    public void testC3P0() throws PropertyVetoException, SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/java_test?useSSL=false&serverTimezone=UTC");
        cpds.setUser("crscd");
        cpds.setPassword("crscd123@");

        LOGGER.info(cpds.getConnection().toString());
    }

    /**
     * 1.加载dbcp的properties配置文件，配置文件的键值来自于BasicDataSource的属性
     * 2.调用BasicDataSourceFactory的createDataSource方法创建DataSource实例
     * 3.从DataSource实例获取数据库连接
     *
     * @throws Exception
     */
    @Test
    public void testDBCPWithDataSourceFactory() throws Exception {
        Properties properties = new Properties();
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(inputStream);
        DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();
        LOGGER.info(connection.getClass().toString());
        connection = dataSource.getConnection();
        LOGGER.info(connection.getClass().toString());
        connection = dataSource.getConnection();
        LOGGER.info(connection.getClass().toString());
        connection = dataSource.getConnection();
        LOGGER.info(connection.getClass().toString());
        Connection connection2 = dataSource.getConnection();
        LOGGER.info(">" + connection.getClass().toString());

        new Thread(() -> {
            try {
                Connection conn = dataSource.getConnection();
                LOGGER.info(">>" + conn.getClass().toString());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }).start();

        Thread.sleep(4000);

        connection2.close();

    }

    /**
     * 使用DBCP数据库连接池
     * 1. 引入依赖jar包，连接池依赖于Commons Pool.jar
     * 2. 创建数据库链接池
     * 3. 为数据源制定必要的属性
     * 4. 从数据源获取数据库连接
     */
    @Test
    public void testDBCP() throws SQLException {
        // 1. 创建DBPC数据源实例
        BasicDataSource dataSource = new BasicDataSource();
        // 2. 为数据源实例指定必须的属性
        dataSource.setUsername("crscd");
        dataSource.setPassword("crscd123@");
        dataSource.setUrl("jdbc:mysql://localhost:3306/java_test?useSSL=false&serverTimezone=UTC");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

        // 3.指定数据源一些可选的属性
        // 1)指定数据库连接池中初始化的连接个数
        dataSource.setInitialSize(3);
        // 2)指定最大连接数
        dataSource.setMaxTotal(50);
        // 3)最小连接数：在数据库连接池中保存的最少的空闲连接的数量
        dataSource.setMinIdle(3);
        // 4)等待数据库连接池分配连接的最大等待时间，单位为ms，超出时间将抛出异常
        dataSource.setMaxWaitMillis(1000);

        // 4.从数据源中获取数据库连接
        Connection connection = dataSource.getConnection();
        LOGGER.info(connection.getClass().toString());
    }


    /**
     * 使用Druid连接池
     *
     * @throws Exception
     */
    @Test
    public void testDruidWithConfigFile() throws Exception {
        Properties properties = new Properties();
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(inputStream);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        LOGGER.info(connection.getClass().toString());
    }

}
